package com.ids.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import com.ids.bean.PendingAttendanceBean;
import com.ids.bean.UserAttendanceData;
import com.ids.util.Constants;
import com.ids.util.DbManager;
import com.mysql.jdbc.Statement;

public class AttendanceScreenDao {
	private static AttendanceScreenDao instance = new AttendanceScreenDao();

	private AttendanceScreenDao() {}

	public static AttendanceScreenDao getInstance() {
		return instance;
	}
	public Map<Integer,String> getAttendanceType(String empCode){
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
//		String attendanceTypeSql = "select * from attendancetype where attendanceTypeId NOT IN ('0', '15');";
		String attendanceTypeSql = " select at.attendanceType, at.attendanceTypeId , lc.count, at.dependency, at.valid "
			+" from attendancetype at, leavecount lc "
			+" where  at.attendanceTypeId NOT IN (select attendanceTypeId from attendanceType where visibility = 'F' ) and empcode= "
			+ empCode
			+" and lc.leaveType = at.dependency and "
			+" lc.count >= at.valid  union select att.attendanceType, att.attendanceTypeId, 0 count , att.dependency , att.valid  " 
			+" from attendancetype att where att.attendanceTypeId NOT IN (select attendanceTypeId from attendanceType where visibility = 'F') and att.dependency = '0';";
		
		Map<Integer,String> attendanceType = getAttendanceTypeList(attendanceTypeSql,empCode, conn,true);
		
		return attendanceType;
	}

	public Map<Integer,String> getDependantAttendanceType(int attendanceTypeId, String empCode) {
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		Map<Integer,String> attendanceType;
//		String attendanceTypeSql = "select at.attendanceTypeId ,  at.attendanceType from attendancetype at , dependantattendancetype dt  where dt.dependantType=at.attendanceTypeId and dt.attendanceType ="
//			+ attendanceTypeId;
		String attendanceTypeSql ="select at.attendanceTypeId ,  at.attendanceType , lc.count , at.dependency , at.valid "
		+" from attendancetype at , dependantattendancetype dt ,  leavecount lc "
		+" where dt.dependantType=at.attendanceTypeId AND empcode= "+empCode+" and "
		+" lc.leaveType = at.dependency and "
		+" lc.count >= at.valid and dt.attendanceType ="+ attendanceTypeId +" and at.visibility = 'T' union select at.attendanceTypeId ,  at.attendanceType , 0 count , at.dependency , at.valid"
		+" from attendancetype at , dependantattendancetype dt where dt.dependantType=at.attendanceTypeId and"  
		+" dt.attendanceType = "+ attendanceTypeId +" and at.dependency = '0' and at.visibility = 'T'";
		String excludeShortLeaveSql = attendanceTypeSql.concat(" and at.attendanceTypeId!="+ Constants.ATTENDANCE_SHORT_LEAVE );
		
		if(attendanceTypeId== Constants.ATTENDANCE_FULL_SHIFT){
			int month = new Date().getMonth() + 1;
			String checkShortLCountSql = "select count(*)  as shortLeaveCount from attendance where dependantattendancetypeId ="+ Constants.ATTENDANCE_SHORT_LEAVE+"  and empCode ="+empCode+" and substr(attendance.dated,6,2) ="+month;
			boolean result =checkShortLCount(checkShortLCountSql);
			if (result){
				 attendanceType = getAttendanceTypeList(attendanceTypeSql,empCode , conn, false);	
			}else{
				 attendanceType = getAttendanceTypeList(excludeShortLeaveSql,empCode, conn,false);
			}
		}else{
			 attendanceType = getAttendanceTypeList(attendanceTypeSql, empCode,  conn, false);
		}
		return attendanceType;
	}
	
	public UserAttendanceData getSelectedAttendance(String date,String empCode){
		String attendanceSql="select attendanceTypeId,dependantAttendanceTypeId from  attendance where dated ='"+date + "' and empCode ="+empCode;
		ResultSet userAttendanceRS = null;	
		Statement stmt = null;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		UserAttendanceData userAttendance = null;
		try{
			stmt = (Statement)conn.createStatement();
			userAttendanceRS = stmt.executeQuery(attendanceSql);
			
			while(userAttendanceRS.next()){
				userAttendance = new UserAttendanceData();
				userAttendance.setAttendanceTypeId(userAttendanceRS.getInt("attendanceTypeId"));
				userAttendance.setDependantAttendanceTypeId(userAttendanceRS.getInt("dependantAttendanceTypeId"));
			}
			if(null==userAttendance){
				userAttendance = new UserAttendanceData();
				userAttendance.setAttendanceTypeId(1);
				userAttendance.setDependantAttendanceTypeId(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}  finally {
			DbManager.closeResultSet(userAttendanceRS);
			DbManager.closeConnection(conn);
		}
		return userAttendance;
	}


//private List<AttendanceType> getAttendanceTypeList(String attendanceTypeSql,  Connection conn ){
//	ResultSet attendanceTypeRS = null;	
//	Statement state = null;
//	List<AttendanceType> attendanceType = null;
//	try{
//		state = (Statement)conn.createStatement();
//		attendanceTypeRS = state.executeQuery(attendanceTypeSql);
//		attendanceType = new ArrayList<AttendanceType>();
//		AttendanceType attendanceTypeBeans = null;
//		while(attendanceTypeRS.next()){
//			attendanceTypeBeans = new AttendanceType();
//			attendanceTypeBeans.setId(attendanceTypeRS.getInt("attendanceTypeId"));
//			attendanceTypeBeans.setValue(attendanceTypeRS.getString("attendanceType"));
//			attendanceType.add(attendanceTypeBeans);
//		}
//	} catch (Exception e) {
//		e.printStackTrace();
//	}  finally {
//	DbManager.closeResultSet(attendanceTypeRS);
//	DbManager.closeConnection(conn);
//	}
//	return attendanceType;
//	
//	}
private Map<Integer,String> getAttendanceTypeList(String attendanceTypeSql, String empCode, Connection conn ,boolean isAttendanceType){
	ResultSet attendanceTypeRS = null;	
	Statement stmtAttendance = null;
	Map<Integer,String> attendanceMap= new TreeMap<Integer, String>();
	List<PendingAttendanceBean> pendingAttData = getPendingAttData(empCode);
	try{
		stmtAttendance = (Statement)conn.createStatement();
		attendanceTypeRS = stmtAttendance.executeQuery(attendanceTypeSql);


		
		while(attendanceTypeRS.next()){
			if(isAttendanceType){
				if(attendanceTypeRS.getInt("attendanceTypeId")==0 ||attendanceTypeRS.getInt("attendanceTypeId")==18 ||attendanceTypeRS.getInt("attendanceTypeId")==19 ){
					continue;
				}
			}
			attendanceMap.put(attendanceTypeRS.getInt("attendanceTypeId"), attendanceTypeRS.getString("attendanceType"));
			String dependency = attendanceTypeRS.getString("dependency");
			
			for(PendingAttendanceBean beans:pendingAttData){
				String depend = beans.getDependency();
				if(dependency.equals(depend)){
					double count = attendanceTypeRS.getDouble("count");
					double pendingCount = beans.getCount();
					double resultingCount = count - pendingCount;
					double validCount = attendanceTypeRS.getDouble("valid");
					if(resultingCount<validCount){
						attendanceMap.remove(attendanceTypeRS.getInt("attendanceTypeId"));
					}
				}
			}
		}
		int noOfTimesPlTaken = getNoOfTimesPlTaken(empCode);
		if(noOfTimesPlTaken>=Constants.NO_OF_TIMES_PL_TAKEN){
			attendanceMap.remove(Constants.ATTENDANCE_ONE_PL);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}  
	return attendanceMap;
	
	}

private static boolean checkShortLCount(String checkShortLCountSql){
	boolean result = false;
	DbManager dbManager = new DbManager();
	Connection conn = dbManager.getConnection();
	ResultSet shortLeaveCountRS = null;	
	Statement shortLeaveStmt = null;
	try{
		shortLeaveStmt = (Statement)conn.createStatement();
		shortLeaveCountRS = shortLeaveStmt.executeQuery(checkShortLCountSql);
		while(shortLeaveCountRS.next()){
			int shortLeaveCount = shortLeaveCountRS.getInt("shortLeaveCount");
			if (shortLeaveCount<2){
				result=true;
			}else{
				result=false;
			}
		}
	} catch (Exception e) {
		e.printStackTrace();
	}  finally {
	DbManager.closeResultSet(shortLeaveCountRS);
	DbManager.closeConnection(conn);
	}
	return result;
	
}

public List<PendingAttendanceBean> getPendingAttData(String empCode){
	String pendingAttendanceTypeSql= "select count(*) * att.valid  count,  att.attendanceTypeId ,att.dependency "
		+" from attendance at,attendancetype att "
		+" where empcode = "+empCode+" and statusid=1 and " 
		+" (at.attendanceTypeId = att.attendanceTypeId  || att.attendanceTypeId = at.dependantattendancetypeid) " 
		+" and att.attendanceTypeId in (select attendanceTypeId  from attendancetype where dependency != '0') group by att.attendanceTypeId; ";
	List<PendingAttendanceBean> pendingAttendanceList = new ArrayList<PendingAttendanceBean>();
	ResultSet pendingAttendanceTypeRS = null;	
	Statement stmtPendingAttendance = null;
	DbManager dbManager = new DbManager();
	Connection conn = dbManager.getConnection();
	try{
		stmtPendingAttendance = (Statement)conn.createStatement();
		pendingAttendanceTypeRS = stmtPendingAttendance.executeQuery(pendingAttendanceTypeSql);
		PendingAttendanceBean pendingAttendanceBean = null;
		while(pendingAttendanceTypeRS.next()){
			pendingAttendanceBean = new PendingAttendanceBean();
			pendingAttendanceBean.setAttendanceTypeId(pendingAttendanceTypeRS.getInt("attendanceTypeId"));
			pendingAttendanceBean.setCount(pendingAttendanceTypeRS.getDouble("count"));
			pendingAttendanceBean.setDependency(pendingAttendanceTypeRS.getString("dependency"));
			pendingAttendanceList.add(pendingAttendanceBean);
		}
	}catch (Exception e) {
			e.printStackTrace();
	}finally {
		DbManager.closeResultSet(pendingAttendanceTypeRS);
		DbManager.closeConnection(conn);
	}
		return pendingAttendanceList;	
	}

   private static int  getNoOfTimesPlTaken(String empCode){
	    Calendar cal = Calendar.getInstance();
	    cal.add(Calendar.DATE, 1);
	    int year=cal.get(Calendar.YEAR);
	    String noOfPls = "select * from attendance where attendancetypeId=" +Constants.ATTENDANCE_ONE_PL+ "  and empCode ="+ empCode+" and  substr(attendance.dated,1,4) = " +year+ " order by dated;";
	    ResultSet noOfPlsRS = null;	
	    Statement stmtNoOfPls = null;
		DbManager dbManager = new DbManager();
		Connection conn = dbManager.getConnection();
		int noOfTimesPlTaken = 0;
		try{
			stmtNoOfPls = (Statement)conn.createStatement();
			noOfPlsRS = stmtNoOfPls.executeQuery(noOfPls);
			java.sql.Date presentdate = null;
			java.sql.Date nextDate = null;
			if(noOfPlsRS.next()){
				presentdate = noOfPlsRS.getDate("dated");
				noOfTimesPlTaken = 1;
			}
			while(noOfPlsRS.next()){
				nextDate = noOfPlsRS.getDate("dated");
				if((nextDate.getTime()-presentdate.getTime())/86400000 > 1){
					++noOfTimesPlTaken;
				}
				presentdate = nextDate;
			}
		}catch (Exception e) {
				e.printStackTrace();
		}finally {
			DbManager.closeResultSet(noOfPlsRS);
			DbManager.closeConnection(conn);
		}
	   return noOfTimesPlTaken;
   }
   public float getPLCount(String empCode){
	   String noOfPlsSql = "select count from leavecount where leaveType ='PL' and empCode ="+empCode;
	   DbManager dbManager = new DbManager();
	   Connection conn = dbManager.getConnection();
	   Statement stmt;
	   ResultSet noOfPlsRs;
	   float count =0.0F;
	   try {
		   stmt = (Statement)conn.createStatement();
		   noOfPlsRs = stmt.executeQuery(noOfPlsSql);
		   while(noOfPlsRs.next()){
				 count = noOfPlsRs.getFloat("count");
			}
	} catch (Exception e) {
		e.printStackTrace();
	}
		
		return count;
	}
  
}

	
	

